﻿using System;
using System.IO;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SQLite;
using System.Net;
using RedFoxUtils;
using System.Diagnostics;

namespace RedFoxScraper
{
  class Database: IDatabase
  {
    private String databasePath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "RedFoxForum.db");
    private String imagesPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "RedFoxForumImages");
    private static IDatabase database = null;

    /// <summary>
    /// Singleton pattern, ensure to get only one Database instance.
    /// </summary>
    /// <returns></returns>
    public static IDatabase getInstance()
    {
      if (database == null)
      {
        database = new Database();
      }
      return database;
    }

    private Database()
    {
      if (!File.Exists(databasePath))
      {
        createDatabase();
      }

      if (!Directory.Exists(imagesPath))
      {
        Directory.CreateDirectory(imagesPath);
      }
    }

    /// <summary>
    /// Initialize tables in database.
    /// </summary>
    /// <param name="connection"></param>
    private void initializeSchemas( IDbConnection connection )
    {
      string[] sqls = new string[] {
        "CREATE TABLE board (id integer primary key, name varchar(128));",
        //"CREATE TABLE post (id integer primary key, boardid integer, ishead boolean, title text, content text, author text, posted datetime);"
        "CREATE TABLE post (id integer, parentid integer, boardid integer, ishead boolean, title text, content text, author text, posted datetime);"
      };

      connection.Open();
      foreach (string sql in sqls)
      {
        var cmd = connection.CreateCommand();
        cmd.CommandText = sql;
        cmd.ExecuteNonQuery();
      }
    }

    /// <summary>
    /// Create database connection.
    /// </summary>
    /// <returns></returns>
    private IDbConnection createConnection()
    {
      return new SQLiteConnection(String.Format("Data source={0}", databasePath));
    }

    /// <summary>
    /// Create database and call initializeSchemas() to initialize tables.
    /// </summary>
    private void createDatabase()
    {
      IDbConnection conn;
      using (conn = createConnection())
      {
        initializeSchemas(conn);
      }
    }

    /// <summary>
    /// Insert a record to Board table.
    /// </summary>
    /// <param name="id"></param>
    /// <param name="name"></param>
    public void insertBorad(int id, string name)
    {
      using (IDbConnection conn = createConnection())
      {
        conn.Open();

        var cmd = conn.CreateCommand();
        cmd.CommandText = "insert into board (id, name ) values ( ?, ? )";
        var pId = cmd.CreateParameter();
        pId.DbType = DbType.Int32;
        pId.Value = id;
        cmd.Parameters.Add(pId);
        var pName = cmd.CreateParameter();
        pName.DbType = DbType.String;
        pName.Value = name;
        cmd.Parameters.Add(pName);
        cmd.ExecuteNonQuery();
      }
    }

    /// <summary>
    /// Get all boards.
    /// </summary>
    /// <returns></returns>
    public List<Tuple<int, string>> getBoards()
    {
      var result = new List<Tuple<int, string>>();
      using (IDbConnection conn = createConnection())
      {
        conn.Open();

        var cmd = conn.CreateCommand();
        cmd.CommandText = "select id, name from board";
        cmd.CommandType = CommandType.Text;
        
        using (IDataReader reader = cmd.ExecuteReader())
        {
          while (reader.Read()) {
            result.Add(
              new Tuple<int, string>( 
                    reader.GetInt32(0), 
                    reader.GetString(1) )
              );
          }
        }
      }
      return result;
    }

    private IDbDataParameter buildParameter( IDbCommand cmd, DbType type, object value )
    {
      IDbDataParameter param = cmd.CreateParameter();
      param.DbType = type;
      param.Value = value;
      return param;
    }

    /// <summary>
    /// Insert post brief information into tables.
    /// 1st floor's parent id is self.
    /// </summary>
    /// <param name="id"></param>
    /// <param name="boardId"></param>
    /// <param name="title"></param>
    /// <param name="author"></param>
    /// <param name="posted"></param>
    public void insertPostBrief( int id, int boardId, string title, string author, DateTime posted ) {
      insertPost(id, id, boardId, true, title, "", author, posted);
    }

    public void updatePost( int id, string content )
    {
      using (IDbConnection conn = createConnection())
      {
        conn.Open();

        var cmd = conn.CreateCommand();
        cmd.CommandText = "update post SET content=? WHERE id=?";
        cmd.Parameters.Add(buildParameter(cmd, DbType.String, content));
        cmd.Parameters.Add(buildParameter(cmd, DbType.Int32, id));
        cmd.ExecuteNonQuery();
      }
    }

    public bool isPostExisted(int id)
    {
      using (IDbConnection conn = createConnection())
      {
        conn.Open();

        var cmd = conn.CreateCommand();
        cmd.CommandText = "SELECT count(*) FROM post WHERE id=?";
        cmd.Parameters.Add(buildParameter(cmd, DbType.Int32, id));
        int count = (int)cmd.ExecuteScalar();
        return count>0;
      }
    }

    public void insertRelatedPost(int id, int parentId, int boardId, string title, string content, string author, DateTime posted)
    {
      insertPost(id, parentId, boardId, false, title, content, author, posted);
    }

    private void insertPost(int id, int parentId, int boardId, bool isHead, string title, string content, string author, DateTime posted)
    {
      using (IDbConnection conn = createConnection())
      {
        conn.Open();

        var cmd = conn.CreateCommand();
        cmd.CommandText = "insert into post (id, parentid, boardid, ishead, title, content, author, posted ) values ( ?, ?, ?, ?, ?, ?, ?, ? )";
        cmd.Parameters.Add(buildParameter(cmd, DbType.Int32, id));
        cmd.Parameters.Add(buildParameter(cmd, DbType.Int32, parentId));
        cmd.Parameters.Add(buildParameter(cmd, DbType.Int32, boardId));
        cmd.Parameters.Add(buildParameter(cmd, DbType.Boolean, isHead));
        cmd.Parameters.Add(buildParameter(cmd, DbType.String, title));
        cmd.Parameters.Add(buildParameter(cmd, DbType.String, content));
        cmd.Parameters.Add(buildParameter(cmd, DbType.String, author));
        cmd.Parameters.Add(buildParameter(cmd, DbType.DateTime, posted));
        cmd.ExecuteNonQuery();
      }
    }

    public List<int> getPostsByBoard(int boardId)
    {
      var result = new List<int>();
      using (IDbConnection conn = createConnection())
      {
        conn.Open();

        var cmd = conn.CreateCommand();
        cmd.CommandText = "select id from post where boardid=? and ishead=1 order by id";
        cmd.CommandType = CommandType.Text;
        cmd.Parameters.Add(buildParameter(cmd, DbType.Int32, boardId));

        using (IDataReader reader = cmd.ExecuteReader())
        {
          while (reader.Read())
          {
            result.Add( reader.GetInt32(0) );
          }
        }
      }
      return result;
    }

    public string saveImage(int id, string url)
    {
      HttpClient client = new HttpClient(Helper.GetUriCookieContainer(new Uri(Scraper.FORUM_BASE)));
      string downloadUrl="";
      if( url.StartsWith("http") )
        downloadUrl = url;
      else
        downloadUrl = Scraper.FORUM_BASE + "/" + url;
      string storePath = Path.Combine(imagesPath, id.ToString());
      if( !Directory.Exists(storePath))
        Directory.CreateDirectory(storePath);
      string filename = new Uri(downloadUrl).LocalPath.Replace("/", "_");
      string storeFile = Path.Combine(storePath, filename);
      try
      {
        client.DownloadFile(downloadUrl, storeFile);

        // upload to redfoxspamdb.appspot.com
        client.Headers[HttpRequestHeader.ContentType] = "application/x-www-form-urlencoded";
        System.Collections.Specialized.NameValueCollection reqparm = new System.Collections.Specialized.NameValueCollection();
        reqparm.Add("id", id.ToString());
        string newUrl = client.UploadFile2("http://redfoxspamdb.appspot.com/a/i/upload", storeFile, reqparm);
        Debug.WriteLine("Upload to newUrl={0}", newUrl);
        return storeFile;
      }
      catch (WebException ex)
      {
        HttpWebResponse errorResponse = ex.Response as HttpWebResponse;
        if (errorResponse.StatusCode == HttpStatusCode.NotFound) {
          Debug.WriteLine("File not found, skip to download.");
        }
      }
      return url;
    }
  }
}
